--创建病房表序列
create sequence seq_ward;

--创建病房表
create table ward(

id number(8) primary key,--序列，主键

ward_num nvarchar2(50) default 'ward_' || substr(sys_guid(),1,12)  not null unique,--病房编号，非空，唯一，默认表名_uuid()前12位。

ward_type number(1),--病房类型。

sickbed_count number(2),--病床数量

medical_office_id number(8),--科室id

is_enable number(1) default 1 ,       -- 是否可用，0：不可用，1：可用 , 默认1

create_date date default sysdate ,     -- 创建时间

create_username nvarchar2(50) ,        -- 创建用户名称

last_update_date date ,				-- 最后一次修改时间

last_update_username nvarchar2(50),   -- 最后一次修改用户名称

delete_date date ,					-- 删除时间

constraint fk_ward_medical_office_id foreign key (medical_office_id) references medical_office (id)--外键约束（medical_office_id）
);

-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,1,4,1);
insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,1,4,2);
insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,1,4,3);
-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,1,4,1);
insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,1,4,2);

-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,2,3,1);
--
-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,2,3,1);

----------
insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,2,3,2);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,2,3,2);
-------------
insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,2,3,3);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,2,3,3);
--
-- --------------重症病房
-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,4,2,1);
--
-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,4,3,1);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,4,3,2);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,4,3,2);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,4,3,3);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,4,3,3);
----------------------------隔离病房
-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,8,2,1);

-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,8,2,1);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,8,2,2);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,8,2,2);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,8,2,3);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,8,2,3);
--------------------------无菌病房
-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,9,2,1);

-- insert into ward(id,ward_type,sickbed_count,medical_office_id)
-- values
-- (seq_ward.nextval,9,2,1);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,9,2,2);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,9,2,2);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,9,2,3);

insert into ward(id,ward_type,sickbed_count,medical_office_id)
values
(seq_ward.nextval,9,2,3);


select * from ward;


commit;